package de.stalinski.db;

import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;

import de.stalinski.util.Crypter;
import de.stalinski.util.Logging;
import java.math.BigDecimal;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import javax.servlet.http.HttpServlet;

/**
 * ConsumersConnector class as a connector to consumers - table
 * @version 1.0.0_0001
 * @author Mario
 */
public class ConsumersConnector extends HttpServlet
{

    private static String _sUsr = "root";

    private static String _sPwd = "admin";

    private static String _sDB = "frbrUsers";

    private static String _sTable = "customers";

    private static Connection _connection;

    private static String _sHost = "localhost";

    private static String _sConn = "jdbc:mysql://" + _sHost + ":3306/" + _sDB;

    private ArrayList<String> _customers = new ArrayList();

    private HashMap _forename = new HashMap();

    private HashMap _surname = new HashMap();

    /**
     * Constructor
     * @throws ClassNotFoundException
     * @trhows SQLException
     */
    public ConsumersConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( _connection == null ) {
                _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public boolean addUser(String fn, String sn, String mail, String un,
            String pass, String admin, String status, String intern) {
        if ( fn.equals("") || sn.equals("") || mail.equals("") || un.equals("") || pass.
                equals("") || admin.equals("") || status.equals("") || intern.
                equals("") ) {
            return false;
        }
        try {
            pass = Crypter.crypt(pass);
        } catch ( Exception ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        String executeValues = "('" + fn + "','" + sn + "','" + mail + "','" + un + "','" + pass + "'," + admin + ",'" + status + "'," + intern + ")";
        String execute = "INSERT INTO " + _sTable + " (Vorname,Nachname,Mail,Username, pass, ISADMIN, Status, INTERN)" +
                "VALUES " + executeValues;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
            return false;
        }

        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            st = _connection.createStatement();
            st.execute(execute);
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
            return false;
        }
        return true;
    }

    public String getUserDropDown(boolean consumption) {
        String out = "<select name=\"consumer\">";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);

            if ( consumption ) {
                out += "<option>Alle</option>";
            }
            Statement st = null;
            ResultSet rs = null;
            try {
                st = _connection.createStatement();
                rs = st.executeQuery(
                        "select * from " + _sTable + " ORDER BY Nachname ASC");
            } catch ( CommunicationsException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
                return "<:jsp forward page=\"index.jsp\"/>";
            }
            ArrayList<String> consumers = new ArrayList<String>();
            while ( rs.next() ) {
                if ( !consumers.contains(rs.getString("Nachname") + ", " + rs.
                        getString("Vorname")) ) {
                    consumers.add(rs.getString("Nachname") + ", " + rs.getString(
                            "Vorname"));
                }
            }
            for ( String s : consumers ) {
                out += "<option>" + s + "</option>";
            }
            out += "</select>";

        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

    public int editUser(String id, String fn, String sn, String un, String mail,
            String pass_one, String pass_two, String admin, String intern,
            String status) {
        try {
            if ( !pass_one.equals(pass_two) ) {
                return 100;
            }
            String hashedPass = Crypter.crypt(pass_two);
            ArrayList<String> updateStrings = new ArrayList();
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            st = _connection.createStatement();
            updateStrings.add(
                    "UPDATE " + _sTable + " SET Vorname='" + fn + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET Nachname='" + sn + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET Username='" + un + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET Mail='" + mail + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET pass='" + hashedPass + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET ISADMIN=" + admin + " WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET Status='" + status + "' WHERE ID='" + id + "'");
            updateStrings.add(
                    "UPDATE " + _sTable + " SET INTERN=" + intern + " WHERE ID='" + id + "'");
            for ( String s : updateStrings ) {
                st.execute(s);
            }
            _connection.close();

        } catch ( Exception ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
            return 200;
        }
        return 0;
    }

    public boolean alreadyExists(String un) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery(
                    "select * from " + _sTable + " WHERE Username='" + un + "'");
            while ( rs.next() ) {
                try {
                    if ( rs.getString("Username").equals(un) ) {
                        throw new SQLException("User existiert bereits");
                    }
                } catch ( SQLException ex ) {
                    Logging.log(Logging.ERROR, ex, this.getClass().getName());
                    _connection.close();
                    return true;
                }
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( !_connection.isClosed() ) {
                _connection.close();
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        return false;
    }

    /**
     * Deletes user for forename, surname, username and id
     * @param fn Forename as String
     * @param sn Surname as String
     * @param un Username as String
     * @param id ID as String
     * @return success - state
     */
    public boolean deleteUser(String fn, String sn, String un, String id) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement stOne = null;
            Statement stTwo = null;
            ResultSet rs = null;

            stOne = _connection.createStatement();
            stTwo = _connection.createStatement();
            rs = stTwo.executeQuery("SELECT * FROM " + _sTable);
            BigDecimal summe = new BigDecimal(0.0);
            while ( rs.next() ) {
                if ( fn.equals(rs.getString("Vorname")) ) {
                    if ( sn.equals(rs.getString("Nachname")) ) {
                        if ( un.equals(rs.getString("Username")) ) {

                            summe = new BookingsConnector().getLiabilityFor(
                                    id,
                                    fn, sn);

                        }
                    }
                }
            }
            if ( summe.doubleValue() <= 0.0 ) {
                stOne.execute(
                        "DELETE FROM " + _sTable + " WHERE Vorname='" + fn + "' AND Nachname='" + sn + "' AND Username='" + un + "' AND ID='" + id + "'");
                _connection.close();
                return true;
            } else {
                try {
                    throw new Exception("Benutzer hat noch Verpflichtungen");
                } catch ( Exception ex ) {
                    Logging.log(Logging.ERROR, ex, this.getClass().getName());
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( !_connection.isClosed() ) {
                _connection.close();
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return false;
    }

    /**
     * Returns the ID for user with Forename, Surname and Username
     * @param fn Forename as String
     * @param sn Surname as String
     * @param un Username as String
     * @return the ID as String
     */
    public String getId(String fn, String sn, String un) {
        String out = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery(
                    "select * from " + _sTable + " WHERE Vorname='" + fn + "' AND Nachname='" + sn + "' AND Username='" + un + "'");

            while ( rs.next() ) {
                if ( rs.getString("Username").equals(un) ) {
                    if ( rs.getString("Vorname").equals(fn) ) {
                        if ( rs.getString("Nachname").equals(sn) ) {
                            out = rs.getString("ID");
                        }
                    }
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( out.equals("") ) {
                throw new SQLException("Es konnte keine ID ermittelt werden");
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

    /**
     * Returns the Username for user with forename and surname
     * @param fn Forename as String
     * @param sn Surname as String
     * @return the Username as String
     */
    public String getUsername(String fn, String sn) {
        String out = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery(
                    "select * from " + _sTable + " WHERE Vorname='" + fn + "' AND Nachname='" + sn + "'");

            while ( rs.next() ) {

                if ( rs.getString("Vorname").equals(fn) ) {
                    if ( rs.getString("Nachname").equals(sn) ) {
                        out = rs.getString("Username");
                    }
                }

            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        try {
            if ( out.equals("") ) {
                throw new SQLException(
                        "Es konnte kein Username ermittelt werden");
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;

    }

    /**
     * Builds the user table for user-overview
     * @return a html - table as String
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public String buildUsersTable() {
        try {
            String out = "<table class=\"Prices\" cellspacing=0><thead>";
            out += "<th>ID</th><th>Vorname</th><th>Name</th><th>Status</th><th>Intern</th><th>Verpflichtungen</th><th>Gezahlt</th><th>Offen</th><th>Mail</th><th>Username</th><th>Admin</th><th>Aktion</th></thead>";
            out += "<tbody>";
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery(
                    "select * from " + _sTable + " ORDER BY Nachname ASC");
            while ( rs.next() ) {
                String id = rs.getString("ID");
                String fn = rs.getString("Vorname");
                String sn = rs.getString("Nachname");
                String un = rs.getString("Username");
                String mail = rs.getString("Mail");
                BigDecimal sum = new BookingsConnector().getLiabilityFor(id, fn,
                        sn);
                BigDecimal paid = new BookingsConnector().getPaidFor(id, fn, sn);
                String pass = rs.getString("Pass");
                String admin = rs.getString("ISADMIN");
                String intern = rs.getString("INTERN");
                String stat = rs.getString("Status");
                out += "<tr bgcolor=\"#FFFFFF\"><td>" + id + "<td>" + fn + "</td><td>" + sn + "</td><td>" + stat + "</td><td>" + intern + "</td>";
                out += "<td>" + sum + "&nbsp;&euro;</td>";
                out += "<td>" + paid + "&nbsp;&euro;</td>";
                out += "<td>" + sum.subtract(paid).
                        setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                out += "<td>" + mail + "</td><td>" + un + "</td>";
                String editLink = "<a class=\"button\" href=\"edit.jsp?id=" + id + "&fn=" + fn + "&sn=" + sn + "&un=" + un + "&pass=" + pass + "&mail=" + mail + "&admin=" + admin + "&intern=" + intern + "&status=" + stat + "\"><span>Edit</span></a>";
                String deleteLink = "<a class=\"button\" href=\"delete.jsp?fn=" + fn + "&sn=" + sn + "&un=" + un + "&id=" + id + "\"><span>Delete</span></a>";
                String bookLink = "<a class=\"button\" href=\"../BOOKING/booking.jsp?fn=" + fn + "&sn=" + sn + "&un=" + un + "&id=" + id + "\"><span>Buchung</span></a>";
                out += "<td>" + admin + "</td>";
                out += "<td>" + editLink + "&nbsp;" + deleteLink + "&nbsp;" + bookLink + "</td></tr>";
            }
            out += "</tbody></table>";
            _connection.close();
            return out;
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return "";
    }

    /**
     * Prooves if the user with username and pass wether is authenticated or not
     * @param user Username as String
     * @param pass Password as String
     * @return the state as boolean:<br><b>true</b> if user is authenticated<br> <b>false</b> if not
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws NuSuchAlgorithmException
     */
    public boolean isAuthenticated(String user, String pass) {
        String hashedPass = "";
        boolean result = false;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " WHERE ISADMIN");

            try {
                hashedPass = Crypter.crypt(pass);
            } catch ( NoSuchAlgorithmException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
            while ( rs.next() ) {
                if ( user.equals(rs.getString("Username")) ) {
                    if ( hashedPass.equals(rs.getString("pass")) ) {
                        _connection.close();
                        result = true;
                        return result;
                    }
                }
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( !_connection.isClosed() ) {
                _connection.close();
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        if ( !result ) {
            try {
                throw new Exception("Benutzer " + user + " ist nicht autorisiert");
            } catch ( Exception ex ) {
                Logging.log(Logging.INFO, ex, this.getClass().getName());
            }
        }
        return result;
    }

    public String getConsumptionForUserAndSort(String fn, String sn, String sort) {
        String result = "";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            String s = sort.replaceAll(" ", "");
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery(
                    "select * from " + _sTable + " WHERE Vorname='" + fn + "' AND Nachname='" + sn + "'");
            if ( rs.equals(null) ) {
                try {
                    throw new Exception("Konsum für Benutzer " + fn + " " + sn + " und Sorte " + sort + " konnte nicht ermittelt werden");
                } catch ( Exception ex ) {
                    Logging.log(Logging.INFO, ex, this.getClass().getName());
                }
            }
            while ( rs.next() ) {
                result += rs.getString(s);
            }
            _connection.close();
            return result;
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( result.equals("") ) {
                throw new SQLException(
                        "Verbrauch von " + sort + " konnte nicht ermittelt werden für Benutzer " + fn + " " + sn);
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return result;
    }

    public ArrayList<String> getMailAddresses() {
        ArrayList<String> result = new ArrayList();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);

            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);
            while ( rs.next() ) {
                String fn = rs.getString("Vorname");
                String sn = rs.getString("Nachname");
                String mail = rs.getString("Mail");
                String putValue = fn + "|" + sn + "|" + mail;
                result.add(putValue);
            }
            _connection.close();
            return result;
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( result.size() == 0 ) {
                throw new SQLException(
                        "Email-Adressen konnten nicht ermittelt werden");
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return result;
    }

    public HashMap<String, Double> getRecipients() {
        HashMap<String, Double> result = new HashMap();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);

            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);
            while ( rs.next() ) {
                Double sum = rs.getDouble("Summe");
                String fn = rs.getString("Vorname");
                String sn = rs.getString("Nachname");
                String mail = rs.getString("Mail");
                String putValue = fn + "|" + sn + "|" + mail;

                if ( sum >= 15.0 ) {
                    result.put(putValue, sum);
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        try {
            if ( result.isEmpty() ) {
                throw new SQLException(
                        "Empfänger konnten nicht ermittelt werden");
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }

        return result;
    }

    public void pay(String fn, String sn, Double value) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);
            try {
                st.execute(
                        "UPDATE " + _sTable + " SET paid=paid" + value + " WHERE Vorname ='" + fn + "' AND Nachname='" + sn + "'");
            } catch ( SQLException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public String getCustomers(String index, String status) {
        String out = "<table><tbody><tr>";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            _customers.clear();

            try {
                Statement st = null;
                ResultSet rs = null;
                st = _connection.createStatement();
                rs = st.executeQuery("select * from " + _sTable);
                int i = 0;
                while ( rs.next() ) {
                    String foundIndex = rs.getString("Nachname").substring(0, 1);
                    String foundStatus = rs.getString("Status");
                    if ( foundIndex.equals(index) && foundStatus.equals(status) ) {
                        String value = rs.getString("Nachname") + ", " + rs.
                                getString("Vorname");
                        _customers.add(value);
                        _forename.put(value, rs.getString("Vorname"));
                        _surname.put(value, rs.getString("Nachname"));
                    }
                }
                Collections.sort(_customers);
                if ( _customers.size() <= 0 ) {
                    return null;
                }
                for ( String s : _customers ) {
                    String link = "drinks.jsp?fn=" + _forename.get(s) + "&sn=" + _surname.
                            get(s);
                    if ( i > 3 ) {
                        out += "<tr>";
                    }
                    out += "<td>" + "<a href=\"" + link + "\">" + s + "</a>" + "</td>";
                    if ( i > 3 ) {
                        i = 0;
                    }
                    i++;
                }
            } catch ( SQLException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
            _connection.close();
            return out += "</tr></tbody></table>";
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

}